﻿CREATE PROCEDURE [dbo].[Text_GetChapterElements]
	@hashTag NVARCHAR(128)
AS
	DECLARE @chapterId UNIQUEIDENTIFIER 

	SELECT @chapterId = [ChapterId] FROM [text_Chapter] WHERE [HashTag] = @hashTag 

	;WITH tree ([ChapterId],[ParentChapterId],[HashTag],[Title],[Level],[Order]) AS
	(
		SELECT [ChapterId]
				,[ParentChapterId]
				,[HashTag]
				,[Title]
				,0
				,[Order]
		FROM [dbo].[text_Chapter]
		WHERE [BookId] = (SELECT [BookId] FROM [dbo].[text_Chapter] WHERE [ChapterId] = @chapterId) AND [ParentChapterId] IS NULL

		UNION ALL

		SELECT c.[ChapterId]
				,c.[ParentChapterId]
				,c.[HashTag]
				,c.[Title]
				,t.[Level] + 1
				,c.[Order]
		FROM [dbo].[text_Chapter] AS c
		INNER JOIN tree AS t ON t.ChapterId = c.[ParentChapterId]
	)

	SELECT [HashTag], [Title], [Order] 
	FROM tree 
	WHERE [Level] = (SELECT [Level] FROM tree WHERE [ChapterId] = @chapterId)
	ORDER BY [Order]


	SELECT [TextId], [Value]
	FROM [dbo].[text_Text]
	WHERE [ChapterId] = @chapterId AND [Value] like N'%<strong>Листинг %'
	ORDER BY [Order]

	SELECT [TextId], [Value]
	FROM [dbo].[text_Text]
	WHERE [ChapterId] = @chapterId AND [Value] like N'%<strong>Рисунок %'
	ORDER BY [Order]

	SELECT [TextId], [Value]
	FROM [dbo].[text_Text]
	WHERE [ChapterId] = @chapterId AND [Value] like N'%<strong>Таблица %'
	ORDER BY [Order]
RETURN 0
